﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Security.Cryptography;

namespace mamnontuoitho7.AppCode
{
    public class Parent : Member
    {

        public ConnectGetData _connectData;
        SqlCommand _cmd;
        String _sql = "";
        General _general;

        public Parent() 
        {
            _connectData = new ConnectGetData();
            _cmd = new SqlCommand(_sql, _connectData.GetConnect());
            _general = new General();
        }


        /// <summary>
        /// Insert information for new parent
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="memberName"></param>
        /// <param name="fullname"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <param name="avatar"></param>
        /// <param name="memberVisible"></param>
        /// <param name="classID"></param>
        /// <returns></returns>
        internal int CreateParent(String memberName, String fullname, String sex, String birthday, String address, String email, String phone, String career)
        {

            //Check Username is existed or not
            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmdCheckUsername = new SqlCommand("select COUNT(MemberID) as CountRecord from Member where MemberName = @memberName ", con);
            cmdCheckUsername.Parameters.AddWithValue("@memberName", memberName);
            int checkUsername = Convert.ToInt16(_connectData.GetDataTable(cmdCheckUsername).Rows[0]["CountRecord"].ToString());
            if (checkUsername > 0)
            {
                return -99;
            }

            int memMaxID = -1;
            string hPassword = _general.ComputeHash(ConstantClass._passwordDefault, new SHA256CryptoServiceProvider());

            SqlCommand cmd = new SqlCommand("sp_ParentInsert", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@roleID", SqlDbType.NVarChar).Value = 4;
            cmd.Parameters.Add("@memberName", SqlDbType.NVarChar).Value = memberName;
            cmd.Parameters.Add("@pass", SqlDbType.NVarChar).Value = hPassword;
            cmd.Parameters.Add("@fullname", SqlDbType.NVarChar).Value = fullname;
            cmd.Parameters.Add("@sex", SqlDbType.NVarChar).Value = sex;
            cmd.Parameters.Add("@birthday", SqlDbType.NVarChar).Value = birthday;
            cmd.Parameters.Add("@address", SqlDbType.NVarChar).Value = address;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;
            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;
            //cmd.Parameters.AddWithValue("@avatar", avatar == null ? "NULL" : avatar);
            cmd.Parameters.Add("@career", SqlDbType.NVarChar).Value = career;

            SqlParameter parameterReturnValue = new SqlParameter("memMaxID", SqlDbType.Int, 4);
            parameterReturnValue.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(parameterReturnValue);

            con.Open();

            cmd.ExecuteNonQuery();
            memMaxID = Int32.Parse(cmd.Parameters["memMaxID"].Value.ToString());
            cmd.Dispose();
            con.Close();

            return memMaxID;
        }


        /// <summary>
        /// Update new information for parent
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="fullname"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <param name="avatar"></param>
        /// <param name="career"></param>
        /// <returns></returns>
        public int UpdateParent(String memberName, String memberID, String fullname, String sex, String birthday, String address, String email, String phone, String avatar, String career, String memberVisible)
        {
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sqlUp = "update Member set MemberName = @memberName, " +
                                                " Fullname = @fullname, " +
                                                " Sex = @sex, " +
                                                " Birthday = @birthday, " +
                                                " Address = @address, " +
                                                " Email = @email, " +
                                                " Phone = @phone, " +
                                                " Avatar = @avatar, " +
                                                " Career = @career, " +
                                                " MemberVisible = @memberVisible " +
                                                " where MemberID = @memberID ";

                SqlCommand cmd = new SqlCommand(sqlUp, con);
                cmd.Parameters.AddWithValue("@memberName", memberName);
                cmd.Parameters.AddWithValue("@fullname", fullname);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@avatar", avatar);
                cmd.Parameters.AddWithValue("@career", career);
                cmd.Parameters.AddWithValue("@MemberVisible", memberVisible);
                cmd.Parameters.AddWithValue("@memberID", memberID);

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();

            }
            catch (SqlException sqlEx)
            {
                System.Diagnostics.Debug.WriteLine(sqlEx.Message);
                return sqlEx.Number;
            }

            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return 0;
            }

            return 1;
        }


        /// <summary>
        /// Update avatar
        /// </summary>
        /// <param name="maxMemID"></param>
        /// <param name="teacherAvatar"></param>
        internal void UpdateAvatar(int maxMemID, string parentAvatar)
        {
            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmd = new SqlCommand("update Member set avatar = @avatar where MemberID = @memberID", con);
            cmd.Parameters.AddWithValue("@avatar", parentAvatar);
            cmd.Parameters.AddWithValue("@memberID", maxMemID);
            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }


        /// <summary>
        /// get a number of record of a page
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetParentPaged(int pageIndex, int step, String fullName, String memberName, String email, String phone, String allKid)
        {

            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;

            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmd = new SqlCommand("sp_ParentList", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@startRowIndex", SqlDbType.NVarChar).Value = startRowIndex;
            cmd.Parameters.Add("@endRowIndex", SqlDbType.NVarChar).Value = endRowIndex;
            cmd.Parameters.Add("@fullName", SqlDbType.NVarChar).Value = fullName;
            cmd.Parameters.Add("@memberName", SqlDbType.NVarChar).Value = memberName;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;

            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;
            cmd.Parameters.Add("@allKid", SqlDbType.NVarChar).Value = allKid;

            DataTable dt = _connectData.GetDataTable(cmd);

            return dt;
        }


        /// <summary>
        /// get total number of article in ONE type
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalParentOfOneSearchTimes(String fullName, String memberName, String email, String phone, String allKid)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();
            SqlCommand cmd = new SqlCommand("sp_CountParentList", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@fullName", SqlDbType.NVarChar).Value = fullName;
            cmd.Parameters.Add("@memberName", SqlDbType.NVarChar).Value = memberName;
            cmd.Parameters.Add("@email", SqlDbType.NVarChar).Value = email;

            cmd.Parameters.Add("@phone", SqlDbType.NVarChar).Value = phone;
            cmd.Parameters.Add("@allKid", SqlDbType.NVarChar).Value = allKid;

            object returnValue;

            returnValue = cmd.ExecuteScalar();

            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }


        /// <summary>
        /// Update information of parent from Parnet page
        /// </summary>
        /// <param name="memberID"></param>
        /// <param name="sex"></param>
        /// <param name="birthday"></param>
        /// <param name="address"></param>
        /// <param name="email"></param>
        /// <param name="phone"></param>
        /// <returns></returns>
        internal bool UpdateParent(String memberID, String sex, String birthday, String address, String email, String phone, String career, String pass)
        {
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sqlUp = "";
                if (pass == "")
                {
                    sqlUp = "update member set" + " sex = @sex, " +
                                                    " birthday = @birthday, " +
                                                    " address = @address, " +
                                                    " email = @email, " +
                                                    " career = @career, " +
                                                    " phone = @phone " +
                                                    " where memberID = @memberID ";
                }
                else
                {
                    sqlUp = "update member set" + " sex = @sex, " +
                                                    " birthday = @birthday, " +
                                                    " address = @address, " +
                                                    " email = @email, " +
                                                    " career = @career, " +
                                                    " pass = @pass, " +
                                                    " phone = @phone " +
                                                    " where memberID = @memberID ";
                }
                SqlCommand cmd = new SqlCommand(sqlUp, con);
                cmd.Parameters.AddWithValue("@sex", sex);
                cmd.Parameters.AddWithValue("@birthday", birthday);
                cmd.Parameters.AddWithValue("@address", address);
                cmd.Parameters.AddWithValue("@email", email);
                cmd.Parameters.AddWithValue("@phone", phone);
                cmd.Parameters.AddWithValue("@career", career);
                cmd.Parameters.AddWithValue("@memberID", memberID);
                if (pass != "")
                {
                    string hPassword = _general.ComputeHash(pass, new SHA256CryptoServiceProvider());
                    cmd.Parameters.AddWithValue("@pass", hPassword);
                }

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Diagnostics.Debug.WriteLine(ex.Message);
                return false;
            }
            return true;
        }

        /// <summary>
        /// check parent availability
        /// </summary>
        /// <returns></returns>
        public String CheckParentAvailability(String ParentID)
        {
            string re = "1";
            string sql = "select KidVisible from dbo.ParentKidListView where KidID  is not null and MemberID = " + ParentID;
           
            DataTable dt = _connectData.GetDataTable(sql);

            if (dt == null)
            {
                //Parent have no kid
                re = "1";
            }
            else {
                for (int i = 0; i < dt.Rows.Count;i++ )
                {

                    if (dt.Rows[i]["KidVisible"].ToString() == "True")
                    {
                        //Parent have a kid that is learning at school
                        re = "2";
                        break;
                    }
                    //All kid of this parent that is out of school
                    re = "3";
                }
            }

            return re;
        }
    }
}